package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import net.sf.json.JSONObject;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.p2p.dao.POStzrDao;
import com.bringway.p2p.dao.TxDao;


/**
 * 
 * @author hc
 * @version 创建时间：2016年2月22日 上午10:34:42
 * @功能：pos界面
 */


@Repository
public class POStzrDaoImpl extends P2PJdbcDao implements POStzrDao {

	/**
	 * @author hc
	 * POStzrDao 的实现类
	 * 获取pos的查询数据
	 */
	public List<Map<String, Object>> queryPostzrList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String sh_start_date = params.optString("sh_start_date");
		String sh_end_date = params.optString("sh_end_date");
		String zq_id = params.optString("zq_id");
		String pos_code = params.optString("pos_code");
		String pos_owner_user_id = params.optString("pos_owner_user_id");
		String sh_state = params.optString("sh_state");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		
		if(StringUtils.isNotEmpty(sh_start_date)){
			sh_start_date=sh_start_date+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(sh_end_date)){
			sh_end_date=sh_end_date+" 23:59:59";
		}
		
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("startDate", startDate);
		paramMap.put("endDate", endDate);
		paramMap.put("sh_start_date", sh_start_date);
		paramMap.put("sh_end_date", sh_end_date);
		paramMap.put("zq_id", zq_id);
		paramMap.put("pos_code", pos_code);
		paramMap.put("pos_owner_user_id", pos_owner_user_id);
		paramMap.put("sh_state", sh_state);
		sql.append(" SELECT t1.ID,t1.POS_ID,t1.POS_CODE,t2.USERID,t1.ZQ_ID,t1.TZJE,date_format(t1.TZSJ,'%Y-%m-%d %H:%i:%S')  TZSJ,t1.SH_USER_ID,t1.SH_DATE, ");
		sql.append(" t1.SH_CONTENT,t1.SH_STATE,t4.OPER_USER_ID,t4.POS_BIZ_GYH,t4.POS_OWNER_USER_ID,t3.SJHM,t3.TJR,t4.POS_PLATFORM,t4.POS_CUSTOMER_SEX,t4.POS_CUSTOMER_SFZ,t4.POS_CUSTOMER_HK_YHMC,t4.POS_CUSTOMER_HK_CARD_NUM,t4.POS_CUSTOMER_LY,now() FSSJ,t5.JXRQ  ");
		sql.append(" FROM t_zhfw_pos_tzrxx t1 left join t_zhfw_tzrxx t2 on t1.POS_CODE = t2.POS_CODE ");
		sql.append(" left join t_zhfw_user t3 on t2.USERID = t3.USER_NAME left join t_zhfw_pos_info t4 on t4.POS_CODE = t1.POS_CODE left join t_zhfw_zqzrlb t5 on t1.ZQ_ID = t5.ID where t1.SH_STATE = '02' AND 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND t1.TZSJ >= :startDate");
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND t1.TZSJ <= :endDate");
		}
		if(StringUtils.isNotEmpty(sh_start_date)){
			sql.append(" AND t1.SH_DATE >= :sh_start_date");
		}
		if(StringUtils.isNotEmpty(sh_end_date)){
			sql.append(" AND t1.SH_DATE <= :sh_end_date");
		}
		if(StringUtils.isNotEmpty(zq_id)){
			sql.append(" AND t1.ZQ_ID like "+ "'%"+zq_id+"%'");
		}
		if(StringUtils.isNotEmpty(pos_code)){
			sql.append(" AND t1.POS_CODE like "+ "'%"+pos_code+"%'");
		}
		if(StringUtils.isNotEmpty(pos_owner_user_id)){
			sql.append(" AND t4.POS_OWNER_USER_ID like "+ "'%"+pos_owner_user_id+"%'");
		}
		if(StringUtils.isNotEmpty(sh_state)){
			sql.append(" AND t1.SH_STATE like "+ "'%"+sh_state+"%'");
		}
		sql.append(" order by TZSJ desc ");
		if(0!=limit){
			sql.append(" LIMIT :START,:LIMIT ");
		}
		System.out.println(sql.toString());
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap, new ResultSetExtractor<List<Map<String, Object>>>(){

			@Override
			public List<Map<String, Object>> extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Map<String, Object>> list = new ArrayList<>();
				while(rs.next()){
					Map<String, Object> map = new HashMap<>();
					String id = "";
					if(StringUtils.isNotEmpty(rs.getString("ID"))){
						id = rs.getString("ID");
					}
					map.put("ID", id);
					String pos_id = "";
					if(StringUtils.isNotEmpty(rs.getString("POS_ID"))){
						pos_id = rs.getString("POS_ID");
					}
					map.put("POS_ID", pos_id);
					String pos_code = "";
					if(StringUtils.isNotEmpty(rs.getString("POS_CODE"))){
						pos_code = rs.getString("POS_CODE");
					}
					map.put("POS_CODE", pos_code);
					String userid = "";
					if(StringUtils.isNotEmpty(rs.getString("USERID"))){
						userid = rs.getString("USERID");
					}
					map.put("USERID", userid);
					String zq_id = "";
					if(StringUtils.isNotEmpty(rs.getString("ZQ_ID"))){
						zq_id = rs.getString("ZQ_ID");
					}
					map.put("ZQ_ID", zq_id);

					String tjr = "";
					if(StringUtils.isNotEmpty(rs.getString("TJR"))){
						tjr = rs.getString("TJR");
					}
					map.put("TJR", tjr);
					map.put("POS_PLATFORM", rs.getString("POS_PLATFORM"));
					String pos_platform = rs.getString("POS_PLATFORM");
					if("00".equals(pos_platform)){
						map.put("POS_PLATFORM", "未知");
					}else if("01".equals(pos_platform)){
						map.put("POS_PLATFORM", "通联");
					}else if("02".equals(pos_platform)){
						map.put("POS_PLATFORM", "银盛");
					}else{
						map.put("POS_PLATFORM", pos_platform);
					}										
					map.put("POS_CUSTOMER_SEX", rs.getString("POS_CUSTOMER_SEX"));
					String pos_customer_sex = rs.getString("POS_CUSTOMER_SEX");
					if("00".equals(pos_customer_sex)){
						map.put("POS_CUSTOMER_SEX", "未知");
					}else if("01".equals(pos_customer_sex)){
						map.put("POS_CUSTOMER_SEX", "男");
					}else if("02".equals(pos_customer_sex)){
						map.put("POS_CUSTOMER_SEX", "女");
					}else{
						map.put("POS_CUSTOMER_SEX", pos_customer_sex);
					}
				
					String pos_customer_sfz = "";
					if(StringUtils.isNotEmpty(rs.getString("POS_CUSTOMER_SFZ"))){
						pos_customer_sfz = rs.getString("POS_CUSTOMER_SFZ");
					}
					map.put("POS_CUSTOMER_SFZ", pos_customer_sfz);
					String pos_customer_hk_yhmc = "";
					if(StringUtils.isNotEmpty(rs.getString("POS_CUSTOMER_HK_YHMC"))){
						pos_customer_hk_yhmc = rs.getString("POS_CUSTOMER_HK_YHMC");
					}
					map.put("POS_CUSTOMER_HK_YHMC", pos_customer_hk_yhmc);
					String pos_customer_hk_card_num = "";
					if(StringUtils.isNotEmpty(rs.getString("POS_CUSTOMER_HK_CARD_NUM"))){
						pos_customer_hk_card_num = rs.getString("POS_CUSTOMER_HK_CARD_NUM");
					}
					map.put("POS_CUSTOMER_HK_CARD_NUM", pos_customer_hk_card_num);

					map.put("POS_CUSTOMER_LY", rs.getString("POS_CUSTOMER_LY"));
					String pos_customer_ly = rs.getString("POS_CUSTOMER_LY");
					if("00".equals(pos_customer_ly)){
						map.put("POS_CUSTOMER_LY", "未知");
					}else if("01".equals(pos_customer_ly)){
						map.put("POS_CUSTOMER_LY", "陌生拜访");
					}else if("02".equals(pos_customer_ly)){
						map.put("POS_CUSTOMER_LY", "驻点");
					}else if("03".equals(pos_customer_ly)){
						map.put("POS_CUSTOMER_LY", "缘故");
					}else if("04".equals(pos_customer_ly)){
						map.put("POS_CUSTOMER_LY", "转介绍");
					}else{
						map.put("POS_CUSTOMER_LY", pos_customer_ly);
					}		

					String jxrq = "";
					if(StringUtils.isNotEmpty(rs.getString("JXRQ"))){
						jxrq = rs.getString("JXRQ");
					}
					map.put("JXRQ", jxrq);

					String pos_owner_user_id = "";
					if(StringUtils.isNotEmpty(rs.getString("POS_OWNER_USER_ID"))){
						pos_owner_user_id = rs.getString("POS_OWNER_USER_ID");
					}
					map.put("POS_OWNER_USER_ID", pos_owner_user_id);
					String tzje = "";
					if(StringUtils.isNotEmpty(rs.getString("TZJE"))){
						tzje = rs.getString("TZJE");
					}
					map.put("TZJE", tzje);
					String oper_user_id = "";
					if(StringUtils.isNotEmpty(rs.getString("OPER_USER_ID"))){
						oper_user_id = rs.getString("OPER_USER_ID");
					}
					map.put("OPER_USER_ID", oper_user_id);
					String pos_biz_gyh = "";
					if(StringUtils.isNotEmpty(rs.getString("POS_BIZ_GYH"))){
						pos_biz_gyh = rs.getString("POS_BIZ_GYH");
					}
					map.put("POS_BIZ_GYH", pos_biz_gyh);
					String sjhm = "";
					if(StringUtils.isNotEmpty(rs.getString("SJHM"))){
						sjhm = rs.getString("SJHM");
					}
					map.put("SJHM", sjhm);
					String tzsj = "";
					if(StringUtils.isNotEmpty(rs.getString("TZSJ"))){
						tzsj = rs.getString("TZSJ");
					}
					map.put("TZSJ", tzsj);
					String sh_user_id = "";
					if(StringUtils.isNotEmpty(rs.getString("SH_USER_ID"))){
						sh_user_id = rs.getString("SH_USER_ID");
					}
					map.put("SH_USER_ID", sh_user_id);
					String sh_date = "";
					if(StringUtils.isNotEmpty(rs.getString("SH_DATE"))){
						sh_date = rs.getString("SH_DATE");
					}
					map.put("SH_DATE",sh_date);
					String sh_content = "";
					if(StringUtils.isNotEmpty(rs.getString("SH_CONTENT"))){
						sh_content = rs.getString("SH_CONTENT");
					}
					map.put("SH_CONTENT", sh_content);
					map.put("SH_STATE", rs.getString("SH_STATE"));
					String shState = rs.getString("SH_STATE");
					if("01".equals(shState)){
						map.put("SH_STATE", "待审核");
					}else if("02".equals(shState)){
						map.put("SH_STATE", "审核成功");
					}else if("03".equals(shState)){
						map.put("SH_STATE", "审核失败");
					}else{
						map.put("SH_STATE", shState);
					}
					list.add(map);
				}
				return list;
			}
			
		});

	}

	/**
	 * @author hc
	 * 获取pos单查询的数据个数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getPostzrCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String sh_start_date = params.optString("sh_start_date");
		String sh_end_date = params.optString("sh_end_date");
		String zq_id=params.optString("zq_id");
		String pos_code=params.optString("pos_code");
		String pos_owner_user_id=params.optString("pos_owner_user_id");
		String sh_state=params.optString("sh_state");
		StringBuffer sql = new StringBuffer();	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("startDate", startDate);
		paramMap.put("endDate", endDate);
		paramMap.put("sh_start_date", sh_start_date);
		paramMap.put("sh_end_date", sh_end_date);
		paramMap.put("ZQ_ID", zq_id);
		paramMap.put("POS_CODE", pos_code);
		paramMap.put("POS_OWNER_USER_ID", pos_owner_user_id);
		paramMap.put("SH_STATE", sh_state);
		sql.append(" SELECT COUNT(*) CT ");
		sql.append(" FROM t_zhfw_pos_tzrxx t1 left join t_zhfw_tzrxx t2 on t1.POS_CODE = t2.POS_CODE left join t_zhfw_user t3 on t2.USERID = t3.USER_NAME left join t_zhfw_pos_info t4 on t4.POS_CODE = t1.POS_CODE  left join t_zhfw_zqzrlb t5 on t1.ZQ_ID = t5.ID where t1.SH_STATE = '02' and 1=1  ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND t1.TZSJ >= :startDate");
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND t1.TZSJ <= :endDate");
		}
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND t1.SH_DATE >= :sh_start_date");
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND t1.SH_DATE <= :sh_end_date");
		}
		if(StringUtils.isNotEmpty(zq_id)){
			sql.append(" AND t1.ZQ_ID like "+ "'%"+zq_id+"%'");
		}
		if(StringUtils.isNotEmpty(pos_code)){
			sql.append(" AND t1.POS_CODE like "+ "'%"+pos_code+"%'");
		}
		if(StringUtils.isNotEmpty(pos_owner_user_id)){
			sql.append(" AND t4.POS_OWNER_USER_ID like "+ "'%"+pos_owner_user_id+"%'");
		}
		if(StringUtils.isNotEmpty(sh_state)){
			sql.append(" AND t1.SH_STATE like "+ "'%"+sh_state+"%'");
		}
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	
}
